This project represents our inaugural analysis of four companies within the commercial sector over a three-year period, from 2021 to 2023, segmented by quarters. The database utilized in this analysis is entirely hypothetical and does not reflect actual figures or pertain to any real-world company.
Through comprehensive data analysis, we have extracted key performance indicators (KPIs) for each of the four companies. Based on these KPIs, we have formulated conclusions and developed strategic recommendations for our organization.
Scenario: I am a financial analyst at Tech Company, which operates in a commercial market comprising four companies. Our company has been experiencing a decline in both product sales and profits over time, unlike our competitors who are enjoying high profitability.
->NOTE: If you have any confusing just read Print Function in the at the Code it will be highly enough.
Below is the original hypothetical database used in this analysis:
library(readxl)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(tidyr)
options(width = 100)
income_statment <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 1)
balance_sheet_Assets <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 2)
balance_sheet_L_S <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 3)
excel_data<-bind_rows(income_statment,balance_sheet_Assets,balance_sheet_L_S)
### Display the data
print(income_statment)## # A tibble: 48 × 12
## Company Quarter Year Revenue COGS Gross_Profit Operating_Expenses EBIT Interest_Expense
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 648721. 365391. 283331. 189537. 93793. 1851.
## 2 Tech Q2 2021 565250. 260978. 304271. 127755. 176516. 4469.
## 3 Tech Q3 2021 758622. 454160. 304462. 172230. 132231. 11493.
## 4 Tech Q4 2021 669067. 350268. 318798. 143990. 174809. 3853.
## 5 Tech Q1 2022 881818. 429210. 452608. 256685. 195924. 5038.
## 6 Tech Q2 2022 518744. 278455. 240289. 121972. 118317. 8259.
## 7 Tech Q3 2022 656602. 344829. 311773. 133764. 178009. 9580.
## 8 Tech Q4 2022 922800. 489665. 433135. 250773. 182361. 6491.
## 9 Tech Q1 2023 639024. 348730. 290295. 178966. 111329. 4776.
## 10 Tech Q2 2023 595526. 336003. 259523. 150406. 109117. 2377.
## # ℹ 38 more rows
## # ℹ 3 more variables: EBT <dbl>, Tax_Expense <dbl>, Net_Income <dbl>print(balance_sheet_Assets)## # A tibble: 48 × 9
## Company Quarter Year Cash Accounts_Receivable Inventory Current_Assets Property_Plant_Equip…¹
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 108280. 67301. 60333. 235914. 443446.
## 2 Tech Q2 2021 74653. 57080. 36565. 168298. 511039.
## 3 Tech Q3 2021 59313. 84926. 59168. 203407. 722988.
## 4 Tech Q4 2021 121790. 108084. 66411. 296284. 429426.
## 5 Tech Q1 2022 55973. 113650. 48630. 218253. 678703.
## 6 Tech Q2 2022 80702. 102817. 33238. 216757. 316051.
## 7 Tech Q3 2022 88388. 127710. 61840. 277938. 612841.
## 8 Tech Q4 2022 67934. 129025. 86907. 283866. 641802.
## 9 Tech Q1 2023 72952. 67319. 69029. 209300. 552724.
## 10 Tech Q2 2023 132511. 92360. 46537. 271407. 451042.
## # ℹ 38 more rows
## # ℹ abbreviated name: ¹Property_Plant_Equipment
## # ℹ 1 more variable: Total_Assets <dbl>print(balance_sheet_L_S)## # A tibble: 48 × 12
## Company Quarter Year Accounts_Payable Short_Term_Debt Current_Liabilities Long_Term_Debt
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 64310. 82956. 147266. 160837.
## 2 Tech Q2 2021 34109. 64510. 98619. 127204.
## 3 Tech Q3 2021 52609. 114660. 167269. 162520.
## 4 Tech Q4 2021 47387. 107439. 154827. 201878.
## 5 Tech Q1 2022 47422. 130525. 177948. 209180.
## 6 Tech Q2 2022 51469. 70189. 121658. 202107.
## 7 Tech Q3 2022 68962. 93160. 162122. 202316.
## 8 Tech Q4 2022 92628. 98568. 191196. 284823.
## 9 Tech Q1 2023 56606. 90380. 146987. 251022.
## 10 Tech Q2 2023 43997. 82808. 126805. 144322.
## # ℹ 38 more rows
## # ℹ 5 more variables: Total_Liabilities <dbl>, Common_Stock <dbl>, Retained_Earnings <dbl>,
## # Shareholders_Equity <dbl>, Total_Liabilities_and_Equity <dbl> Tech <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 1)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Speed <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 2)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Maclr <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 3)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Dain <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 4)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`print(Tech)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12 ...13
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023 <NA> <NA> <NA>
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
## 3 Revenue 648721 565250 758622 669067 881818 518744 656602 9228… 6390… 5955… 8699… 7310…
## 4 COGS 365391 260978 454160 350268 429210 278455 344829 4896… 3487… 3360… 3902… 4364…
## 5 Gross_Profit 283331 304271 304462 318798 452608 240289 311773 4331… 2902… 2595… 4797… 2946…
## 6 Operating_Expenses 189537 127755 172230 143990 256685 121972 133764 2507… 1789… 1504… 2122… 2128…
## 7 EBIT 93793 176516 132231 174809 195924 118317 178009 1823… 1113… 1091… 2674… 81743
## 8 Interest_Expense 1851 4469 11493 3853 5038 8259 9580 6491 4776 2377 12389 9481
## 9 EBT 91943 172047 120738 170956 190885 110058 168429 1758… 1065… 1067… 2550… 72262
## 10 Tax_Expense 27583 51614 36222 51287 57266 33017 50529 52761 31966 32022 76516 21679
## # ℹ 23 more rowsprint(Speed)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12 ...13
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023 <NA> <NA> <NA>
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
## 3 Revenue 657220 980669 551447 787047 641032 563315 994683 6580… 5749… 6772… 9009… 5206…
## 4 COGS 333620 576060 314556 347393 308822 310093 502147 3449… 3306… 2827… 3868… 2308…
## 5 Gross_Profit 283331 404609 236891 439655 332209 253222 492537 3130… 2442… 3944… 5140… 2897…
## 6 Operating_Expenses 186260 127755 163765 234290 171591 156534 286637 1428… 1323… 1391… 2163… 1178…
## 7 EBIT 93793 276854 73126 205365 160618 96688 205899 1702… 1119… 2553… 2977… 1719…
## 8 Interest_Expense 7072 4469 12872 6518 3458 9235 7179 13524 2566 6512 13653 7282
## 9 EBT 130267 158890 60254 198846 157160 87454 198721 1567… 1093… 2488… 2840… 1646…
## 10 Tax_Expense 39080 47667 18076 59654 47148 26236 59616 47029 32806 74642 85227 49403
## # ℹ 23 more rowsprint(Maclr)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12 ...13
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023 <NA> <NA> <NA>
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
## 3 Revenue 575230 588116 830948 610934 621120 844889 573364 8495… 8109… 6345… 5259… 7235…
## 4 COGS 309856 331783 495346 327718 297236 459040 306862 3521… 4031… 2562… 2906… 3484…
## 5 Gross_Profit 283331 256333 335602 283216 323884 385850 266502 4974… 4077… 3782… 2353… 3751…
## 6 Operating_Expenses 155277 127755 231040 174450 173750 226940 153783 2185… 1947… 1372… 1486… 1574…
## 7 EBIT 93793 128578 104562 108765 150134 158910 112719 2788… 2130… 2409… 86632 2176…
## 8 Interest_Expense 9862 4469 5103 4117 6447 13040 6570 7506 7960 3816 5566 16794
## 9 EBT 100234 117683 99459 104649 143687 145870 106149 2713… 2050… 2371… 81066 2008…
## 10 Tax_Expense 30070 35305 29838 31395 43106 43761 31845 81409 61521 71144 24320 60261
## # ℹ 23 more rowsprint(Dain)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12 ...13
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023 <NA> <NA> <NA>
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
## 3 Revenue 582244 600186 950352 970666 512620 562047 690394 8925… 7864… 9166… 7448… 8832…
## 4 COGS 316333 334554 556525 471660 214573 268560 402282 5346… 3462… 4559… 3622… 4041…
## 5 Gross_Profit 283331 265632 393827 499005 298047 293487 288113 3578… 4402… 4606… 3826… 4790…
## 6 Operating_Expenses 174002 127755 272291 243956 145761 136949 147043 2096… 2197… 1958… 1665… 2256…
## 7 EBIT 93793 137877 121536 255049 152286 156538 141069 1482… 2204… 2647… 2160… 2533…
## 8 Interest_Expense 9789 4469 4735 19283 2081 8831 9876 7790 6859 4909 4101 12799
## 9 EBT 82120 139069 116801 235767 150205 147707 131194 1404… 2136… 2598… 2119… 2405…
## 10 Tax_Expense 24636 41721 35040 70730 45061 44312 39358 42136 64083 77964 63579 72175
## # ℹ 23 more rowsAfter refining and adjusting the values, we are now ready to proceed with the analysis.
In the file provided below, you will find THREE SHEETS:
DATA <- read_excel("D:/Project/Measures.xlsx", sheet = 1)
print(DATA)## # A tibble: 48 × 27
## Company Year `Current Ratio` `Quick Ratio` `Debt-To-equity` `Debt-To-Assets` `Debt-To-Captial`
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 TECH 21-Q1 1.60 1.19 0.657 0.359 0.396
## 2 TECH 21-Q2 1.71 1.34 0.423 0.282 0.297
## 3 TECH 21-Q3 1.22 0.862 0.465 0.299 0.317
## 4 TECH 21-Q4 1.91 1.48 0.838 0.426 0.456
## 5 TECH 22-Q1 1.23 0.953 0.666 0.379 0.400
## 6 TECH 22-Q2 1.78 1.51 1.30 0.511 0.566
## 7 TECH 22-Q3 1.71 1.33 0.561 0.332 0.360
## 8 TECH 22-Q4 1.48 1.03 0.853 0.414 0.460
## 9 TECH 23-Q1 1.42 0.954 0.938 0.448 0.484
## 10 TECH 23-Q2 2.14 1.77 0.503 0.314 0.335
## # ℹ 38 more rows
## # ℹ 20 more variables: `Interest-Coverage` <dbl>, `Recivables Turnover` <dbl>, DSO <dbl>,
## # `Payables Turnover` <dbl>, DPO <dbl>, `Inventory Turnover` <dbl>, DOH <dbl>,
## # `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>, `EBIT Margin` <dbl>,
## # `Tax Burden` <dbl>, `Interest Burden` <dbl>, `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>,
## # `Return-On-Assets` <dbl>, `Equity-Multiplier` <dbl>, `Return-On-Equity` <dbl>,
## # `Return-On-Total-Capital` <dbl>, `Operating-Return-On-Assets` <dbl>, …Changes <- read_excel("D:/Project/Measures.xlsx", sheet = 2)
print(Changes)## # A tibble: 48 × 27
## Company `CHANGES %` `Current Ratio` `Quick Ratio` `Debt-To-equity` `Debt-To-Assets`
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Tech 21-Q1 0 0 0 0
## 2 Tech 21-Q2/21-Q1 0.0653 0.120 -0.356 -0.214
## 3 Tech 21-Q3/21-Q2 -0.287 -0.354 0.0990 0.0602
## 4 Tech 21-Q4/21-Q3 0.574 0.722 0.804 0.425
## 5 Tech 22-Q1/21-Q4 -0.359 -0.358 -0.205 -0.111
## 6 Tech 22-Q2/22-Q1 0.453 0.583 0.955 0.349
## 7 Tech 22-Q3/22-Q2 -0.0378 -0.116 -0.569 -0.351
## 8 Tech 22-Q4/22-Q3 -0.134 -0.227 0.519 0.249
## 9 Tech 23-Q1/22-Q4 -0.0409 -0.0736 0.100 0.0817
## 10 Tech 23-Q2/23-Q1 0.503 0.858 -0.463 -0.298
## # ℹ 38 more rows
## # ℹ 21 more variables: `Debt-To-Captial` <dbl>, `Interest-Coverage` <dbl>,
## # `Recivables Turnover` <dbl>, DSO <dbl>, `Payables Turnover` <dbl>, DPO <dbl>,
## # `Inventory Turnover` <dbl>, DOH <dbl>, `Cash Conversion Cycle` <dbl>,
## # `Gross Profit Margin` <dbl>, `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## # `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>, `Return-On-Assets` <dbl>,
## # `Equity-Multiplier` <dbl>, `Return-On-Equity` <dbl>, `Return-On-Total-Capital` <dbl>, …Which Contain Four Main Measures:
##
## Attaching package: 'plotly'## The following object is masked from 'package:ggplot2':
##
## last_plot## The following object is masked from 'package:stats':
##
## filter## The following object is masked from 'package:graphics':
##
## layout## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'Speed: Speed’s dominance has allowed it to impose strict controls on Accounts Receivable, enabling the company to collect its funds promptly at the end of each quarter. For instance, if a customer purchases on account for $100,000 at the beginning of Q1 2021, they have until the end of Q1 2022 to settle the amount due. This practice contributes to fluctuations in the Cash Conversion Cycle each year. These restrictions effectively enhance the company’s liquidity and activity ratios, providing it with greater debt accessibility which enhances the Net_Profit_Margin.
Maclr: In contrast to the previous company, Maclr does not impose any restrictions on Accounts Receivables. This is evident from the low Receivable Turnover over the years, which has significantly impacted the Cash Conversion Cycle by extending it. This extension is detrimental to any commercial company aiming to shorten the cycle for quick cash collection and inventory purchases. Consequently, this has also negatively affected the Activity Ratios, showing a decline over the years.
Dain: Dain faces a problem similar to Maclr, as it does not appear to impose any restrictions on Accounts Receivables. The consequences of this have already been discussed in the case of Maclr. One significant difference is that Dain has a high inventory turnover, indicating it sells its products quickly, almost surpassing the dominant company, Speed. This efficiency has also helped Dain reduce its Cash Conversion Cycle. Additionally, Dain has managed to extend its Payable Turnover without adversely affecting its operations over the years. Therefore, its only issue lies in the Receivable Turnover.
Tech: The issue is the lack of restrictions on Accounts Receivables, despite stable inventory turnover rates and an increase in Payable Turnover. The lack of controls on Accounts Receivables has significantly extended the Cash Conversion Cycle due to the decrease in Receivable Turnover Ratio. This situation poses a greater risk to the company, particularly if bad debt and the Allowance for Doubtful Accounts increase, which could severely weaken its liquidity ratios.
## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'Speed: it is also on a slight downward trend and maintain more stability compared to Dain and Tech.
Maclr: It shows a slight downward trend but is relatively stable and maintain more stability compared to Dain and Tech.
Dain: It is on a downward trend and has the highest fluctuations in the Current Ratio, particularly in Q1 of 2021.
Tech: It exhibits a clear downward trend and a consistent decline, indicating potential liquidity issues.
## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'Speed: Regarding the company’s debt, the Solvency Ratios indicate that the company leverages its market power and dominance to increase its borrowing capacity. This has significantly impacted the Net Profit Margin, as the higher level of debt has effectively reduced the company’s tax burden.
Maclr: Regarding Solvency, despite the cash flow issue, the company’s Net Profit Margin remains stable and does not decline. This is because the company heavily relies on external debt rather than operating cash flow and liquidity. Although this strategy has reduced taxes and increased net income, it places the company at high risk of bankruptcy if it fails to meet its debt obligations. This issue is further highlighted by the declining Profitability Ratios over the coming years, confirming the problem of reduced operating efficiency.
Dain: Regarding debt, Dain is similar to Maclr, except for the bankruptcy risk. Dain has sufficient liquidity from its operating activities to manage its debt obligations, especially short-term debts.
Tech: Although the company does not rely on debt to the same extent as others, except for the last quarter of the previous year, it still fails to show upward progress in Net Profit Margin trends. This lack of improvement will impact the stability of its operating systems
## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'The graphs indicate that all companies exhibit an upward trend in their Margin Ratios, with increases observed each quarter. However, Tech stands out with a different, downward trend. Additionally, it is important to note that Speed Company is the dominant player in this context.
## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'Speed: In terms of profitability, the key indicators, ROE (Return on Equity) and OROA (Operating Return on Operating Assets), show positive performance for two reasons. Firstly, OROA remains strong because the company has maintained stable inventory turnover (meaning the period in which the inventory is quickly sold). Secondly, ROE has remained stable due to the improvement and consistency in the Dupont Analysis.
Maclr & Dain: They are relatively stable in their Operating Return on Assets (OROA) measure. However, Dain’s Return on Equity (ROE) shows an upward trend due to its high efficiency in increasing its ROE. This efficiency suggests that Dain’s ROE could match that of Maclr in the coming years.
Tech: It appears to be the worst-case scenario compared to other companies in its sector, particularly due to its downward trend in returns from operating activities.
## `geom_smooth()` using formula = 'y ~ x'## `geom_smooth()` using formula = 'y ~ x'All companies are interconnected in terms of costs, as they operate within the same commercial sector and benefit from stable economic conditions.
Tech: Now, addressing the crucial point, the company has declining Margins despite the surrounding companies not experiencing similar issues. If we look at the Gross Profit Margin, it is lower compared to other companies. This could be due to one of two reasons: either the company is not well-known and thus sells less, which is unlikely since the data shows that all four companies have nearly equal profits, or the second and more likely reason is that Tech’s product pricing is higher than that of other companies. Upon investigating, it was found that Tech has higher Costs of Goods Sold compared to the other companies.
Regarding the EBIT Margin (Earnings Before Interest and Tax), it has been low over the years, also due to higher Operating Expenses than the other companies. Ultimately, this has resulted in a lower Net Profit Margin. Additionally, the lower debt levels compared to other companies have led to higher taxes for Tech compared to its peers.
1-Cost Analysis and Value Engineering: The first step Tech should take is to thoroughly examine its costs by implementing value engineering techniques. This involves identifying and eliminating all non-value-added activities to reduce overall costs. By doing so, the company can lower its product prices, thereby increasing its Gross Profit Margin and enhancing its inventory turnover.
2-Implementing Appropriate Controls on Accounts Receivables: After reducing prices, the company should introduce suitable controls on Accounts Receivables that align with its market position. This strategy will help increase the Receivable Turnover, thereby shortening the Cash Conversion Cycle. Consequently, this will improve liquidity and boost operating activities.
3-Optimizing Debt-to-Equity Ratio: Once the company stabilizes and its market value increases, it can leverage the third crucial factor: optimizing its Debt-to-Equity ratio. By aligning its debt levels with its financial position and liquidity at that time, the company can increase its Net Profit Margin and benefit from a reduction in taxes.
Implementing these strategies will enhance Tech’s competitiveness with Speed and other Companies and improve its overall financial performance.